package com.example.dbdoc.strategy;

import com.example.dbdoc.model.ColumnInfo;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 * PostgreSQL数据库元数据查询策略
 * 
 * @author 系统生成
 * @version 1.0.0
 */
public class PostgreSQLMetadataStrategy implements DatabaseMetadataStrategy {
    
    private static final Logger logger = LoggerFactory.getLogger(PostgreSQLMetadataStrategy.class);
    
    @Override
    public String getTableComment(Connection connection, String tableName) {
        String tableComment = "";
        String sql = "SELECT obj_description(c.oid) AS table_comment " +
                    "FROM pg_class c " +
                    "JOIN pg_namespace n ON n.oid = c.relnamespace " +
                    "WHERE c.relname = ? AND n.nspname = ?";
        
        try {
            String schema = getSchema(connection);
            try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                stmt.setString(1, tableName);
                stmt.setString(2, schema);
                
                try (ResultSet rs = stmt.executeQuery()) {
                    if (rs.next()) {
                        tableComment = rs.getString("table_comment");
                        if (tableComment == null) {
                            tableComment = "";
                        }
                    }
                }
            }
            
            logger.debug("表 {} 的注释: {}", tableName, tableComment);
            
        } catch (SQLException e) {
            logger.warn("获取表 {} 注释时发生错误: {}", tableName, e.getMessage());
        }
        
        return tableComment;
    }
    
    @Override
    public void enhanceColumnInfo(Connection connection, String tableName, Map<String, ColumnInfo> columnMap) {
        String sql = "SELECT " +
                    "    a.attname AS column_name, " +
                    "    col_description(a.attrelid, a.attnum) AS column_comment, " +
                    "    a.attnum AS ordinal_position " +
                    "FROM pg_attribute a " +
                    "JOIN pg_class c ON a.attrelid = c.oid " +
                    "JOIN pg_namespace n ON c.relnamespace = n.oid " +
                    "WHERE c.relname = ? " +
                    "  AND n.nspname = ? " +
                    "  AND a.attnum > 0 " +
                    "  AND NOT a.attisdropped " +
                    "ORDER BY a.attnum";
        
        try {
            String schema = getSchema(connection);
            try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                stmt.setString(1, tableName);
                stmt.setString(2, schema);
                
                Map<String, ColumnInfo> orderedColumnMap = new LinkedHashMap<>();
                
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        String columnName = rs.getString("column_name");
                        String columnComment = rs.getString("column_comment");
                        int ordinalPosition = rs.getInt("ordinal_position");
                        
                        ColumnInfo column = columnMap.get(columnName);
                        if (column != null) {
                            column.setComment(columnComment);
                            orderedColumnMap.put(columnName, column);
                            logger.debug("字段排序: {} - 位置: {} - 注释: {}", columnName, ordinalPosition, columnComment);
                        }
                    }
                }
                
                columnMap.clear();
                columnMap.putAll(orderedColumnMap);
            }
            
        } catch (SQLException e) {
            logger.warn("获取表 {} 字段注释和顺序时发生错误: {}", tableName, e.getMessage());
        }
    }
    
    @Override
    public String formatDefaultValue(String defaultValue, String dataType) {
        if (defaultValue == null || defaultValue.trim().isEmpty()) {
            return "";
        }
        
        // 处理序列（nextval）
        if (defaultValue.contains("nextval(")) {
            return "序列";
        }
        
        // 处理类型转换格式：'value'::type
        if (defaultValue.contains("::")) {
            // 提取 :: 之前的值
            String value = defaultValue.substring(0, defaultValue.indexOf("::")).trim();
            // 去除单引号
            if (value.startsWith("'") && value.endsWith("'")) {
                value = value.substring(1, value.length() - 1);
            }
            return value;
        }
        
        return defaultValue;
    }
    
    /**
     * 从默认值中提取序列名称
     * 
     * @param defaultValue 默认值，如：nextval('app_version_ctl_id_seq'::regclass)
     * @return 序列名称，如：app_version_ctl_id_seq
     */
    public static String extractSequenceName(String defaultValue) {
        if (defaultValue == null || !defaultValue.contains("nextval(")) {
            return null;
        }
        
        try {
            // 提取 nextval('sequence_name'::regclass) 中的 sequence_name
            int start = defaultValue.indexOf("'") + 1;
            int end = defaultValue.indexOf("'", start);
            if (start > 0 && end > start) {
                return defaultValue.substring(start, end);
            }
        } catch (Exception e) {
            logger.warn("提取序列名称失败: {}", defaultValue);
        }
        
        return null;
    }
    
    /**
     * 获取当前schema
     */
    private String getSchema(Connection connection) throws SQLException {
        String schema = connection.getSchema();
        if (schema == null || schema.isEmpty()) {
            schema = "public"; // PostgreSQL默认schema
        }
        return schema;
    }
}
